﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ZLDC.Common.Estate;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;

namespace ZLDC.DAL.Estate
{
    public class DeptDAL
    {
        private static string sKey = "estate-deptall";
        private static string strKey = "estate-lhdetpall";
        public static DataSet GetDeptData()
        {
            DataSet ds;
            if (System.Web.HttpContext.Current.Cache[sKey] != null)
            {
                ds = (DataSet)System.Web.HttpContext.Current.Cache[sKey];
            }
            else
            {
                string sSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where dltflg = 0";
                sSql += " and id not in (" + ZLDC.Common.Estate._Utility.GetWebConfigSetting("OutsideDeptId") + ")"; sSql += " and fgs in " + _Utility.GetCompanyNameList();

                using (_Database db = new _Database("zlhq_user"))
                {
                    ds = db.Query(sSql);
                }

                if (ds != null)
                    _Cache.SetCache(sKey, ds, 15 * 60);
            }
            return ds;
        }

        public static DataSet GetDeptData(string skey)
        {
            DataSet ds;
            if (System.Web.HttpContext.Current.Cache[skey] != null)
            {
                ds = (DataSet)System.Web.HttpContext.Current.Cache[skey];

            }
            else
            {
                string sSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where dltflg = 0";
                sSql += " and id not in (" + ZLDC.Common.Estate._Utility.GetWebConfigSetting("OutsideDeptId") + ")"; sSql += " and (py like N'" + skey + "%' or  name like N'%" + skey + "%')";
                sSql += " and fgs in " + _Utility.GetCompanyNameList();

                using (_Database db = new _Database("zlhq_user"))
                {
                    ds = db.Query(sSql);
                }

                if (ds != null)
                    _Cache.SetCache(skey, ds, 15 * 60);
            }
            return ds;
        }

        public static DataSet GetLHDeptData(string skey)
        {
            DataSet ds;
            if (System.Web.HttpContext.Current.Cache[skey] != null)
            {
                ds = (DataSet)System.Web.HttpContext.Current.Cache[skey];

            }
            else
            {
                string sSql = "select id,name,grade,parent_id from [192.168.168.101].zlhq.t_zlhq_t_s_alldept ";
                sSql += " where id not in (" + ZLDC.Common.Estate._Utility.GetWebConfigSetting("OutsideDeptId") + ")"; sSql += " and id in (select distinct deptId From t_quantification)";
                sSql += " and (py like N'" + skey + "%' or  name like N'%" + skey + "%')";
                sSql += " and fgs in " + _Utility.GetCompanyNameList();

                using (_Database db = new _Database("estate"))
                {
                    ds = db.Query(sSql);
                }

                if (ds != null)
                    _Cache.SetCache(skey, ds, 15 * 60);
            }
            return ds;
        }


        public static void ClearCache()
        {
            System.Web.HttpContext.Current.Cache.Remove(sKey);
            System.Web.HttpContext.Current.Cache.Remove(strKey);
        }

        public static string GetDeptNameById(int deptid)
        {
            DataSet ds = null;
            string deptName = "";

            string sSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where id = " + deptid + " and fgs in " + _Utility.GetCompanyNameList();

            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                deptName = ds.Tables[0].Rows[0]["name"].ToString();
            }

            return deptName;
        }

        public static string GetParentIdById(int deptid)
        {
            DataSet ds = null;
            string ParentId = "";

            string sSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where id = " + deptid + " and fgs in " + _Utility.GetCompanyNameList();

            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(sSql);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                try
                {
                    ParentId = ds.Tables[0].Rows[0]["parent_id"].ToString();
                }
                catch { ParentId = "0"; }
            }

            return ParentId;
        }

        public static DataTable getGetAllChildrenDept(string deptid)
        {
            DataSet ds = new DataSet();

            string strSql = "";
            if (deptid != "")
                strSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where DltFlg=0 and Id in(select * from dbo.f_GetAllChildDeptAll(" + deptid + "))";
            else
                strSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where DltFlg=0 and id = " + _Convert.ToInt(deptid, 0);

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static DataTable getGetAllChildrenDeptByType(string deptid, string type)
        {
            DataSet ds = new DataSet();

            string strSql = "";
            if (deptid != "")
                strSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where Id in(select deptid from dbo.f_GetAllChildDeptAll("
                    + deptid + ")) and id in (select distinct deptid from t_quantification)";
            else
                strSql = "select id,name,grade,parent_id from t_zlhq_t_s_alldept where DltFlg=0 and id = " + _Convert.ToInt(deptid, 0);

            if (type != "")
                strSql = strSql + " and type='" + type + "'";

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static DataTable getGetAllChildrenDept(string deptid, string type)
        {
            DataSet ds = new DataSet();

            string strSql = "";
            if (type == "")
            {
                if (ZLDC.Common.BaseUser.UserInfo.GetPopedom("业务员转客(权限)") || ZLDC.Common.BaseUser.UserInfo.GetPopedom("业务员转盘(权限)"))
                {
                    deptid = ConfigurationManager.AppSettings["SalesDepartment"].ToString();
                }
                string IdList = ZLDC.Common.BaseUser.UserInfo.getAllChildDept(deptid, "0"); strSql = "select id,name,grade,parent_id,substring(py,1,1) + name deptname from [192.168.168.101].zlhq.t_zlhq_t_s_alldept where Id in(" + IdList + ") and DltFlg=0 and fgs in " + _Utility.GetCompanyNameList() + " order by substring(py,1,1)";
            }
            else if (type == "分行")
            {
                strSql = "select id,name,grade,parent_id,substring(py,1,1) + name deptname from [192.168.168.101].zlhq.t_zlhq_t_s_alldept where  dltflg=1 and  type='分行' and id is not null and fgs in " + _Utility.GetCompanyNameList() + " and Id in (select distinct markudeptid from t_housing) order by substring(py,1,1)";
            }
            else
            {
                strSql = "select id,name,grade,parent_id,substring(py,1,1) + name deptname from [192.168.168.101].zlhq.t_zlhq_t_s_alldept where  dltflg=0 and  type  in ('分行','区域') and id is not null and fgs in " + _Utility.GetCompanyNameList() + " order by substring(py,1,1)";
            }

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static DataTable getGetChildDept(string deptid)
        {
            DataSet ds = new DataSet();
            string strSql = @"select id,name,grade,parent_id  from t_zlhq_t_s_alldept where DltFlg=0 and parent_id ='" + deptid + "' order by grade";

            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(strSql);
            }
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static void GetUserListForTran(HtmlSelect lc, string DeptID, int tranType)
        {
            if (DeptID.Trim() == "")
            {
                return;
            }

            String strSql = "select id, [Name] + '/' + isnull(no,'') as [user_Name] from t_user where tmp_id is not null and dept_id = " + DeptID;
            if (tranType == 0)
            {
                strSql += " and id in (select distinct (markuid) from t_housing);";
            }
            else
            {
                strSql += " and id in (select distinct (ownerid) from t_customer where dltFlag = 0);";
            }
            DataSet ds = new DataSet();
            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            lc.DataSource = ds;
            lc.DataTextField = "user_Name";
            lc.DataValueField = "id";
            lc.DataBind();
        }

        public static void GetDelUserList(DropDownList lc, string DeptID)
        {
            if (DeptID.Trim() == "")
            {
                return;
            }

            string strSql = "select distinct markUId as id,markuser +'/' + CAST(isnull(markUId,'') as varchar) as [user_Name] from t_housing where dltflag=0 and  markudeptid=" + DeptID + " union select distinct ownerid,owner +'/' + CAST(isnull(ownerid,'') as varchar)  from t_customer where dltflag=0 and ownerdeptId=" + DeptID + "";

            DataSet ds = new DataSet();
            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            lc.DataSource = ds;
            lc.DataTextField = "user_Name";
            lc.DataValueField = "id";
            lc.DataBind();
        }

        public static void GetUserList(HtmlSelect lc, string DeptID)
        {
            if (DeptID.Trim() == "")
            {
                return;
            }

            String strSql = "select no, name from v_s_usr_lp1 where id is not null and t_id = " + DeptID + " and status <> '离职' and DltFlg=0";

            DataSet ds = new DataSet();
            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(strSql);
            }

            lc.DataSource = ds;
            lc.DataTextField = "name";
            lc.DataValueField = "no";
            lc.DataBind();
        }

        public static void GetUserList(DropDownList lc, string DeptID)
        {
            if (DeptID.Trim() == "")
            {
                return;
            }

            String strSql = "select no, name from v_s_usr_lp1 where id is not null and t_id = " + DeptID + " and status <> '离职' and DltFlg=0";

            DataSet ds = new DataSet();
            using (_Database db = new _Database("zlhq_user"))
            {
                ds = db.Query(strSql);
            }

            lc.DataSource = ds;
            lc.DataTextField = "name";
            lc.DataValueField = "no";
            lc.DataBind();
        }

        public static DataTable getDeptInfo(int deptid)
        {
            DataTable dt = null;
            string sql = "select * from t_zlhq_t_s_alldept where id = " + deptid;

            using (_Database db = new _Database("zlhq_user"))
            {
                DataSet ds = db.Query(sql);

                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }

            return dt;
        }

        public static string GetDeptType(string deptid)
        {
            string type = "";
            string sql = "select type from t_zlhq_t_s_alldept where id = " + deptid;
            using (_Database db = new _Database("zlhq_user"))
            {
                DataSet ds = db.Query(sql);

                if (ds != null && ds.Tables.Count > 0)
                    type = ds.Tables[0].Rows[0]["type"].ToString();
            }
            return type;
        }


        public static string GetDeptUsageList(string deptid)
        {
            string cache_key = "cache_dept_" + deptid;
            string usagelist = "";

            if (_Cache.GetCache(cache_key) == null || _Cache.GetCache(cache_key).ToString() == "")
            {
                string sql = "select usagelist from t_zlhq_t_s_alldept where id = " + deptid;
                using (_Database db = new _Database("zlhq_user"))
                {
                    DataSet ds = db.Query(sql);

                    if (ds != null && ds.Tables.Count > 0)
                        usagelist = ds.Tables[0].Rows[0]["usagelist"].ToString();

                    if (usagelist != "")
                    {
                        _Cache.SetCache(cache_key, usagelist, 3600 * 1);
                    }
                }
            }
            else
            {
                usagelist = _Cache.GetCache(cache_key).ToString();
            }
            return usagelist;
        }
    }
}
